package org.builder.excelutil;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.OutputStream;

/**
 * @author nxg
 */
@SuppressWarnings("deprecation")
public class HSSFExcelUtils {

    private HSSFPrintSetup print;
    private final HSSFWorkbook book;
    private short size;
    private HSSFSheet sheet;
    private HSSFRow row;
    private HSSFCell cell;
    private int sheetIndex = -1;
    private final HSSFFont font;

    /**
     * 设置字体大小，打印左右边距，打印方向，设置A4纸张
     */
    public HSSFExcelUtils(int len, String name) {
        if (len < 0) {
            len = 1;
        }
        size = (short) len;
        book = new HSSFWorkbook();
        if (name == null || name.trim().length() == 0) {
            name = "down";
        }
        sheet = book.createSheet();
        sheetIndex++;
        book.setSheetName(sheetIndex, name);

        font = book.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        font.setColor(Font.COLOR_NORMAL);
        // 设置字体大小
        font.setFontHeightInPoints((short) 12);

        // 设置打印左边距
        sheet.setMargin(Sheet.LeftMargin, (short) 0);
        // 设置打印右边距
        sheet.setMargin(Sheet.RightMargin, (short) 0);

        print = sheet.getPrintSetup();
        // 设置打印页面方向
        print.setLandscape(true);
        // 设置A4纸张
        print.setPaperSize(PrintSetup.A4_PAPERSIZE);
    }

    /**
     * 设置打印左右边距，打印方向，设置A4纸张
     *
     * @param leftMargin  左边距
     * @param rightMargin 右边距
     * @param landscape   页面方向 纵向=false 横向=true
     */
    public void createSheet(int len, String name, int leftMargin, int rightMargin, boolean landscape) {
        if (len < 0) {
            len = 1;
        }
        if (name == null || name.trim().length() == 0) {
            name = "down";
        }
        size = (short) len;
        sheet = book.createSheet();
        sheetIndex++;
        book.setSheetName(sheetIndex, name);
        // 设置打印左边距
        sheet.setMargin(Sheet.LeftMargin, (short) leftMargin);
        // 设置打印右边距
        sheet.setMargin(Sheet.RightMargin, (short) rightMargin);
        print = sheet.getPrintSetup();
        // 设置打印页面方向
        print.setLandscape(landscape);
        // 设置A4纸张
        print.setPaperSize(PrintSetup.A4_PAPERSIZE);
    }

    /**
     * 设置标题
     */
    public void setTitle(String title, int iRow) {
        // 设置样式
        HSSFCellStyle hcs = book.createCellStyle();
        hcs.setFont(font);
        hcs.setAlignment(CellStyle.ALIGN_CENTER);

        // 设置单元格属性
        row = sheet.createRow(iRow);
        cell = row.createCell(0);
        cell.setCellValue(title);
        cell.setCellStyle(hcs);
        sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, size - 1));
    }

    public void setRowHeight(int iRow, int height) {
        row = sheet.getRow(iRow);
        if (row == null) {
            row = sheet.createRow(iRow);
        }
        row.setHeight((short) height);
    }


    public HSSFCellStyle getCellStyle(int fillBackGroundColor, int fillForeGroundColor,
                                      int fillPattern, boolean showGridLine) {
        HSSFCellStyle cs = book.createCellStyle();
        cs.setWrapText(true);// 文本自动换行
        cs.setAlignment(CellStyle.ALIGN_CENTER);
        cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        if (fillBackGroundColor > -1) {
            cs.setFillBackgroundColor((short) fillBackGroundColor);
            cs.setFillForegroundColor((short) fillForeGroundColor);
            cs.setFillPattern((short) fillPattern);
        }
        cs.setFont(font);
        if (showGridLine) {
            cs.setBorderBottom((short) 1);
            cs.setBorderLeft((short) 1);
            cs.setBorderRight((short) 1);
            cs.setBorderTop((short) 1);
        }
        return cs;
    }

    public void setMergedCell(HSSFCellStyle cs, int iRow, int iCol, String value,
                              int rowHeight) {
        row = sheet.getRow(iRow);
        if (row == null) {
            row = sheet.createRow(iRow);
        }
        if (rowHeight != -1) {
            row.setHeight((short) rowHeight);
        }
        cell = row.getCell((short) iCol);
        if (cell == null) {
            cell = row.createCell((short) iCol);
        }
        cell.setCellValue(value);
        cell.setCellStyle(cs);
//        sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, iCol, iCol));
    }

    /**
     * 设置超链接单元格
     */
    public void setHyperLinkCell(HSSFCellStyle cs, int iRow, int iCol, String value,
                                 int rowHeight, String hyperlink) {
        CreationHelper createHelper = book.getCreationHelper();
        Hyperlink link = createHelper.createHyperlink(HSSFHyperlink.LINK_URL);

        row = sheet.getRow(iRow);
        if (row == null) {
            row = sheet.createRow(iRow);
        }
        if (rowHeight != -1) {
            row.setHeight((short) rowHeight);
        }
        cell = row.getCell((short) iCol);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cs);
        cell.setCellValue(value);

        link.setAddress(hyperlink);
        cell.setHyperlink(link);

        sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, iCol, iCol));
    }


    public void setCellWidth(int columnIndex, int width) {
        sheet.setColumnWidth((short) columnIndex, (short) width);
    }

    /**
     * @param out
     */
    public void excel(OutputStream out) {
        try {
            book.write(out);
            out.close();
        } catch (Exception e) {
            System.out.println("=====" + e.toString());
        }
    }


    public void setDisplayGrid(boolean show) {
        sheet.setDisplayGridlines(show);
    }

    public HSSFSheet getSheet() {
        return sheet;
    }

}
